﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using SISCON.Dao;
using SISCON.Model;
using log4net;
using SISCON.Common;

namespace SISCON.Dao
{
    public class TeacherDAO : CommonDAO
    {
        private static ILog log = LogManager.GetLogger(typeof(TeacherDAO));

        /// <summary>
        /// Obtiene un maestro de la base de datos especificado por teacherId
        /// </summary>
        /// <param name="teacherId">Id interno del maestro buscado</param>
        /// <returns></returns>
        public Teacher GetTeacher(long teacherId)
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;
            Teacher teacher=new Teacher();
            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                if (teacherId > 0)
                {
                    string sql = "SELECT * FROM sis_teacher WHERE teacher_id=@teacherId";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.Parameters.Add("@teacherId", SqlDbType.BigInt);
                    cmd.Parameters["@teacherId"].Value = teacherId;
                    reader = cmd.ExecuteReader();

                }

                while (reader.Read())
                {
                    teacher = new Teacher();
                    teacher.TeacherId = getReaderLong(reader, @"teacher_id");
                    teacher.Name = getReaderString(reader, @"name");
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return teacher;
        }

        /// <summary>
        /// Obtiene un maestro de la base de datos especificado por su nomina
        /// </summary>
        /// <param name="schoolId">Nomina del maestro buscado</param>
        /// <returns></returns>
        public Teacher GetTeacher(string schoolId)
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;
            Teacher teacher = new Teacher();
            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                if (schoolId != null && schoolId.Trim() != "")
                {
                    string sql = "SELECT * FROM sis_teacher WHERE schoolId = @schoolId";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.Parameters.Add(getNewParameter("@schoolId", SqlDbType.NVarChar, 40, schoolId));
                    reader = cmd.ExecuteReader();
                }

                while (reader.Read())
                {
                    teacher = new Teacher();
                    teacher.TeacherId = getReaderLong(reader, @"teacher_id");
                    teacher.Name = getReaderString(reader, @"name");
                }

                reader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return teacher;
        }

        /// <summary>
        /// Obtiene todos los maestros almacenados en la base de datos
        /// </summary>
        /// <returns>Lista de maestros</returns>
        public List<Teacher> GetAllTeachers()
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;
            List<Teacher> teacherList = new List<Teacher>();

            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();
                string sql = @"SELECT * FROM sis_teacher ORDER BY name asc";
                SqlCommand cmd = new SqlCommand(sql, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Teacher teacher = new Teacher();
                    teacher.TeacherId = getReaderLong(reader, @"teacher_id");
                    teacher.Name = getReaderString(reader, @"name");
                    teacherList.Add(teacher);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
            return teacherList;
        }

        /// <summary>
        /// Guarda un nuevo maestro dentro de la base de datos
        /// </summary>
        /// <param name="teacherId">Id interno del maestro</param>
        /// <param name="name">Nombre del maestro</param>
        /// <param name="schoolId">Nomina del maestro</param>
        public void SaveTeacher(string teacherId, string name, string schoolId, long userId)
        {
            SqlConnection conn = null;
       
            try{
                conn = new SqlConnection(_ConnStr);
                conn.Open();
                if (teacherId != null && !teacherId.Equals("0"))
                {
                    string sql = "UPDATE sis_teacher SET name = @name, school_id = @schoolId, created_by = @createdBy WHERE teacher_id=@teacherId";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.Parameters.Add(getNewParameter("@teacherId", SqlDbType.BigInt, 0, long.Parse(teacherId)));
                    cmd.Parameters.Add(getNewParameter("@name", SqlDbType.NVarChar, 50, name));
                    cmd.Parameters.Add(getNewParameter("@schoolId", SqlDbType.NVarChar, 40, schoolId));
                    cmd.Parameters.Add(getNewParameter("@createdBy", SqlDbType.BigInt, 0, userId));
                    cmd.Prepare();

                    cmd.ExecuteNonQuery();
                }
                else
                {
                    string sql = "INSERT INTO sis_teacher(name, school_id, created_by) VALUES(@name, @schoolId, @createdBy)";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    cmd.Parameters.Add(getNewParameter("@name", SqlDbType.NVarChar, 50, name));
                    cmd.Parameters.Add(getNewParameter("@schoolId", SqlDbType.NVarChar, 40, schoolId));
                    cmd.Parameters.Add(getNewParameter("@createdBy", SqlDbType.BigInt, 0, userId));
                    cmd.Prepare();

                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlEx)
            {
                if (sqlEx.Number == 2601)
                {
                    throw new SISCONException("El nombre de usuario ya existe. Favor de verificar");
                }
                else
                {
                    throw sqlEx;
                }
            }
            catch(Exception ex){
                throw ex;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// Elimina un maestro de la base de datos especificado por teacherId
        /// </summary>
        /// <param name="teacherId">Id interno del maestro</param>
        public void DeleteTeacher(string teacherId)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();
                string sql = "DELETE FROM sis_teacher WHERE teacher_id = @teacherId";
                SqlCommand sqlCommand = new SqlCommand(sql, conn);
                sqlCommand.Parameters.Add("@teacherId", SqlDbType.BigInt);
                sqlCommand.Parameters["@teacherId"].Value = long.Parse(teacherId);
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// Guarda una nueva instancia de la tabla CourseTeacher dentro de la base de datos
        /// </summary>
        /// <param name="courseId">Id interno del curso</param>
        /// <param name="schoolId">Nomina del maestro</param>
        public void SaveCourseTeacher(long courseId, string schoolId)
        {
            SqlCommand sqlCommand = null;
            SqlConnection conn = null;
            SqlTransaction trx = null;

            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                trx = conn.BeginTransaction();

                sqlCommand = new SqlCommand(null, conn, trx);
                sqlCommand.CommandText = "INSERT INTO sis_course_teacher (course_id, school_id) VALUES (@courseId, @schoolId)";
                sqlCommand.Parameters.Add(getNewParameter("@courseId", SqlDbType.BigInt, 0, courseId));
                sqlCommand.Parameters.Add(getNewParameter("@schoolId", SqlDbType.NVarChar, 40, schoolId));

                sqlCommand.Prepare();
                sqlCommand.ExecuteNonQuery();

                trx.Commit();
            }
            catch (Exception ex)
            {
                log.Error("Error in SaveCourseTeacher()", ex);
                throw ex;
            }
            finally
            {
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// Obtiene todos los maestros en los que su nombre coincide con teacherName
        /// </summary>
        /// <param name="teacherName">Nombre del maestro</param>
        /// <returns>Lista de cursos</returns>
        public List<Course> GetTeacherByName(string teacherName, long userId)
        {
            SqlConnection conn = null;
            SqlDataReader reader = null;
            List<Course> lstCourses = new List<Course>();
            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();
                string sql = " SELECT * FROM sis_course sc";
                sql +=          " LEFT JOIN sis_course_schedule scs ON sc.course_id = scs.course_id ";
                sql +=          " LEFT JOIN sis_schedule ss ON scs.schedule_id = ss.schedule_id  ";
                sql +=          " LEFT JOIN sis_course_teacher sct ON sc.course_id = sct.course_id ";
                sql +=          " LEFT JOIN sis_teacher st ON sct.school_id = st.school_id ";
                sql +=          @"WHERE sc.created_by = @userId AND st.name LIKE @teacherName COLLATE Latin1_general_CI_AI ";
                sql +=          @"ORDER BY st.teacher_id ASC";

                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(getNewParameter("@userId", SqlDbType.BigInt, 0, userId));
                cmd.Parameters.Add(getNewParameter("@teacherName", SqlDbType.NVarChar, 45, "%"+teacherName+"%"));
                reader = cmd.ExecuteReader();

                Course course = null;
                Teacher teacher = null;
                Schedule schedule = null;

                HashSet<string> teachersSet = null;
                HashSet<string> scheduleSet = null;
                    
                while (reader.Read()){
                    if (course == null || course.CourseId != getReaderLong(reader, "course_id"))
                    {
                        course = new Course();
                        course.CourseId = getReaderLong(reader, "course_id");
                        course.CourseCode = getReaderString(reader, "course_code");
                        course.CourseName = getReaderString(reader, "course_name");
                        course.CourseNumber = getReaderString(reader, "course_number");
                        course.IsActive = getReaderBool(reader, "is_active");

                        course.Teachers = new List<Teacher>();
                        course.Schedules = new List<Schedule>();
                        teachersSet = new HashSet<string>();
                        scheduleSet = new HashSet<string>();
                        teacher = null;
                        schedule = null;
                        lstCourses.Add(course);
                    }
                            teacher = new Teacher();
                            teacher.TeacherId = getReaderLong(reader, "teacher_id");
                            teacher.SchoolId = getReaderString(reader, "school_id");
                            teacher.Name = getReaderString(reader, "name");

                            teachersSet.Add(teacher.TeacherId.ToString());
                            course.Teachers.Add(teacher);
                        

                        if ((schedule == null || schedule.ScheduleId != getReaderLong(reader, "schedule_id")) && !scheduleSet.Contains(getReaderLong(reader, "schedule_id").ToString()))
                        {
                            schedule = new Schedule();
                            schedule.ScheduleId = getReaderLong(reader, "schedule_id");
                            schedule.Days = getReaderCommaSeparatedString(reader, "days");
                            schedule.StartsAt = getReaderDateTime(reader, "starts_at");
                            schedule.EndsAt = getReaderDateTime(reader, "ends_at");
                            schedule.Classroom = new Classroom()
                            {
                                ClassroomLocation = getReaderString(reader, "classroom_location"),
                                ClassroomNumber = getReaderString(reader, "classroom_number")
                            };

                            scheduleSet.Add(schedule.ScheduleId.ToString());
                            course.Schedules.Add(schedule);
                        }
                    }
                  reader.Close();             
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (reader != null && !reader.IsClosed)
                    {
                        reader.Close();
                    }
                    if (conn != null && conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
             return lstCourses;  
            }

        /// <summary>
        /// Obtiene todos los maestros cuya nomina coincide con schoolId
        /// </summary>
        /// <param name="schoolId">Nomina del maestro</param>
        /// <returns></returns>
        public List<Course> GetTeacherBySchoolId(string schoolId, long userId){
            SqlConnection conn = null;
            SqlDataReader reader = null;
            List<Course> lstCourses = new List<Course>();
            try
            {
                conn = new SqlConnection(_ConnStr);
                conn.Open();

                string sql = " SELECT * FROM sis_course sc";
                sql += " LEFT JOIN sis_course_schedule scs ON sc.course_id = scs.course_id ";
                sql += " LEFT JOIN sis_schedule ss ON scs.schedule_id = ss.schedule_id  ";
                sql += " LEFT JOIN sis_course_teacher sct ON sc.course_id = sct.course_id ";
                sql += " LEFT JOIN sis_teacher st ON sct.school_id = st.school_id ";
                sql += " WHERE sc.created_by = @userId AND st.school_id = @schoolId";
                sql += " ORDER BY st.school_id ASC ";

                SqlCommand sqlCommand = new SqlCommand(sql, conn);
                sqlCommand.Parameters.Add(getNewParameter("@userId", SqlDbType.BigInt, 0, userId));
                sqlCommand.Parameters.Add(getNewParameter("@schoolId", SqlDbType.NVarChar, 35, schoolId));
                reader = sqlCommand.ExecuteReader();

                Course course = null;
                Teacher teacher = null;
                Schedule schedule = null;

                HashSet<string> teachersSet = null;
                HashSet<string> scheduleSet = null;

                while (reader.Read())
                {
                    if (course == null || course.CourseId != getReaderLong(reader, "course_id"))
                    {
                        course = new Course();

                        course.CourseId = getReaderLong(reader, "course_id");
                        course.CourseCode = getReaderString(reader, "course_code");
                        course.CourseName = getReaderString(reader, "course_name");
                        course.CourseNumber = getReaderString(reader, "course_number");
                        course.IsActive = getReaderBool(reader, "is_active");

                        course.Teachers = new List<Teacher>();
                        course.Schedules = new List<Schedule>();

                        teachersSet = new HashSet<string>();
                        scheduleSet = new HashSet<string>();

                        teacher = null;
                        schedule = null;

                        lstCourses.Add(course);
                    }

                    if (course.IsActive)
                    {
                        if ((teacher == null || teacher.TeacherId != getReaderLong(reader, "teacher_id")) && !teachersSet.Contains(getReaderLong(reader, "teacher_id").ToString()))
                        {
                            teacher = new Teacher();
                            teacher.TeacherId = getReaderLong(reader, "teacher_id");
                            teacher.SchoolId = getReaderString(reader, "school_id");
                            teacher.Name = getReaderString(reader, "name");

                            teachersSet.Add(teacher.TeacherId.ToString());
                            course.Teachers.Add(teacher);
                        }

                        if ((schedule == null || schedule.ScheduleId != getReaderLong(reader, "schedule_id")) && !scheduleSet.Contains(getReaderLong(reader, "schedule_id").ToString()))
                        {
                            schedule = new Schedule();
                            schedule.ScheduleId = getReaderLong(reader, "schedule_id");
                            schedule.Days = getReaderCommaSeparatedString(reader, "days");
                            schedule.StartsAt = getReaderDateTime(reader, "starts_at");
                            schedule.EndsAt = getReaderDateTime(reader, "ends_at");
                            schedule.Classroom = new Classroom()
                            {
                                ClassroomLocation = getReaderString(reader, "classroom_location"),
                                ClassroomNumber = getReaderString(reader, "classroom_number")
                            };

                            scheduleSet.Add(schedule.ScheduleId.ToString());
                            course.Schedules.Add(schedule);
                        }
                    }
                }
                reader.Close();
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return lstCourses;
        }

    }
}